﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DataAccessLayer
{
    using DataObjectLayer;
    using BusinessObjectLayer;
    using DataConvertor;
    
    public class PaperDAL
    {
        private static SqlCommand sqlCommand;
        private static PaperDOL.QueryStatus queryStatus;

        public PaperDAL(PaperDOL.QueryStatus QueryStatusField)
        {
            queryStatus = QueryStatusField;
            sqlCommand = new SqlCommand();
            sqlCommand.CommandText = SetCommandText();
        }
        public PaperDAL(PaperDOL.QueryStatus QueryStatusField, PaperBOL _PaperBOL)
        {
            queryStatus = QueryStatusField;
            sqlCommand = new SqlCommand();
            sqlCommand.CommandText = SetCommandText();
            SetParameter(_PaperBOL);
        }
        public PaperBOL[] Select()
        {
            DataTable table = ExecuteReader();
            DataRow row;
            if (table.Rows.Count > 0)
            {
                PaperBOL[] PaperBOLs = new PaperBOL[table.Rows.Count];
                for (int index = 0; index < table.Rows.Count; index++)
                {
                    row = table.Rows[index];
                    short? PaperId = DataConvertor.ConvertToShort(row["PAPERID"]);
                    string PaperTitle = DataConvertor.ConvertToString(row["PAPERTITLE"]);
                    string ImageURL = DataConvertor.ConvertToString(row["IMAGEURL"]);
                    string FileURL = DataConvertor.ConvertToString(row["FILEURL"]);
                    string Description = DataConvertor.ConvertToString(row["DESCRIPTION"]);
                    PaperBOLs[index] = new PaperBOL(PaperId, PaperTitle, Description,FileURL,ImageURL);
                }
                return PaperBOLs;
            }
            return null;
        }
        public void Insert()
        {
            ExecuteNonQuery();
        }
        public void Update()
        {
            ExecuteNonQuery();
        }
        public void Delete()
        {
            ExecuteNonQuery();
        }
        public long? SelectMaxId()
        {
            return DataConvertor.ConvertToLong(ExecuteScalar());
        }
        private string SetCommandText()
        {
            switch (queryStatus)
            {
                case PaperDOL.QueryStatus.Select:
                    return PaperDOL.Select;
                case PaperDOL.QueryStatus.SelectComboBox:
                    return PaperDOL.SelectComboBox;
                case PaperDOL.QueryStatus.Delete:
                    return PaperDOL.Delete;
                case PaperDOL.QueryStatus.Update:
                    return PaperDOL.Update;
                case PaperDOL.QueryStatus.Insert:
                    return PaperDOL.Insert;
                case PaperDOL.QueryStatus.SelectMaxId:
                    return PaperDOL.SelectMaxId;
            }
            return string.Empty;
        }
        private void SetParameter(PaperBOL _PaperBOL)
        {
            sqlCommand.Parameters.Add("@PaperId", SqlDbType.TinyInt).Value = ValidateParameter("@PaperId", _PaperBOL.PaperId, true);
            sqlCommand.Parameters.Add("@PaperTitle", SqlDbType.NVarChar).Value = ValidateParameter("@PaperTitle", _PaperBOL.PaperTitle, true);
            sqlCommand.Parameters.Add("@Description", SqlDbType.NVarChar).Value = ValidateParameter("@Description", _PaperBOL.Description, true);
            sqlCommand.Parameters.Add("@FileURL", SqlDbType.NVarChar).Value = ValidateParameter("@FileURL", _PaperBOL.FileURL, true);
            sqlCommand.Parameters.Add("@ImageURL", SqlDbType.NVarChar).Value = ValidateParameter("@ImageURL", _PaperBOL.ImageURL, true);
        }
        private void ExecuteNonQuery()
        {
            ConnectionDAL _ConnectionDAL = new ConnectionDAL();
            sqlCommand.Connection = _ConnectionDAL.OpenConnection();
            sqlCommand.ExecuteNonQuery();
            _ConnectionDAL.CloseConnection();
        }
        private object ExecuteScalar()
        {
            ConnectionDAL _ConnectionDAL = new ConnectionDAL();
            sqlCommand.Connection = _ConnectionDAL.OpenConnection();
            object Object = sqlCommand.ExecuteScalar();
            _ConnectionDAL.CloseConnection();
            return Object;
        }
        private static DataTable ExecuteReader()
        {
            ConnectionDAL _ConnectionDAL = new ConnectionDAL();
            sqlCommand.Connection = _ConnectionDAL.OpenConnection();
            DataTable dataTable = new DataTable();
            dataTable.Load(sqlCommand.ExecuteReader());
            _ConnectionDAL.CloseConnection();
            return dataTable;
        }
        private static object ValidateParameter(string ParameterName, object Value, bool IsNullable)
        {
            if (Value == null && !IsNullable)
                throw new ArgumentNullException(ParameterName);
            return (Value == null ? DBNull.Value : Value);
        }
        public PaperBOL[] SelectComboBox()
        {
            DataTable table = ExecuteReader();
            DataRow row;
            if (table.Rows.Count > 0)
            {
                PaperBOL[] PaperBOLs = new PaperBOL[table.Rows.Count];
                for (int index = 0; index < table.Rows.Count; index++)
                {
                    row = table.Rows[index];
                    short? PaperId = DataConvertor.ConvertToShort(row["PaperID"]);
                    string PaperTitle = DataConvertor.ConvertToString(row["PaperTITLE"]);                  
                    PaperBOLs[index] = new PaperBOL(PaperId, PaperTitle,null,null,null);
                }
                return PaperBOLs;
            }
            return null;
        }
    }
}
